from datetime import datetime
from typing import Type, List, Sequence, Optional
from emi.bar.data import Interval
from emi.core.DBSource import DBSource
import peewee
from emi.rdp.model import BarDataModel


def split_datetime(time:datetime):
    return int(time.year * 10000 + time.month*100 + time.day), int(time.hour * 10000 + time.minute*100 + time.second)

def merge_datetime(year_month_day:int,hour_mintue_second:int):
    year =  int(year_month_day / 10000  + 0.01)
    month = int((year_month_day / 100) % 100+ 0.01)
    day = int(year_month_day % 100+ 0.01)
    hour = int(hour_mintue_second / 10000+ 0.01)
    minute = int((hour_mintue_second / 100) % 100+ 0.01)
    second = int(hour_mintue_second % 100+ 0.01)
    return  datetime(year = year,month = month,day = day,hour = hour,minute = minute,second = second)

def to_datetime(yy_mm_dd:int,the_day_end = False)->datetime:
    year = int(yy_mm_dd / 10000 + 0.01)
    month = int((yy_mm_dd / 100 + 0.01)) % 100
    day = yy_mm_dd % 100
    if the_day_end:
        return datetime(year=year, month=month, day=day, hour=23, minute=59, second=59)
    else:
        return datetime(year=year, month=month, day=day, hour=0, minute=0, second=0)

class BarDataDB:


    def __init__(self,db:DBSource,table_name='today_bar_data'):
        self.table_name = table_name
        indexes_desc = (
                (("code", "interval", "yy_mm_dd",'hh_mm'), True),
            )
        self.source = db.init_peewee(table_name,BarDataModel,indexes=indexes_desc,ignore_inited=True)

    def get_day_list(self, symbol:str = None,start:datetime = None,end:datetime = None) -> Sequence[datetime]:
        """"
        加载股票symbol的行情日
        """
        year_month_day_list = self.load_day_list_with_yy_mm_sss(symbol,start,end)
        return [ merge_datetime(y_m_d,0) for y_m_d in year_month_day_list]

    def get_code_list(self) -> Sequence[str]:
        """
        返回所有的symbol列表
        """
        with self.source.use_peewee() as model:
            s = (model.select(model.code).distinct())
        return [v.code for v in s]

    def get_hhmm_list(self, day: datetime) -> List[int]:
        yy_mm_dd,h_m_s = split_datetime(day)
        with self.source.use_peewee() as model:
            s = (
                model.select(model.hh_mm)
                    .where(
                    (model.yy_mm_dd == yy_mm_dd)
                )
                    .order_by(model.hh_mm).distinct()
            )
        return [v.hh_mm for v in s]

    def get_bars_by_time(self,time:datetime,hh_mm:int =None)->List[BarDataModel]:
        yy_mm_dd,h_m_s = split_datetime(time)
        if hh_mm is None:
            hh_mm = h_m_s / 100
        with self.source.use_peewee() as model:
            s = (
                model.select()
                    .where(
                    (model.yy_mm_dd == yy_mm_dd)
                    & (model.hh_mm == hh_mm)
                )
                    .order_by(model.hh_mm)
            )
            data = [db_bar for db_bar in s]
        return data



    def get_bars(self, symbol: str,day:datetime,interval:Interval = None)->List[BarDataModel]:
        """
        返回股票的历史行情。
        参数：
            start ： 开始时间，包含当天
            end：   结束时间：包含当天
            bar_type： BarData的子类，默认为BarData
        """

        yy_mm_dd,h_m_s = split_datetime(day)
        with self.source.use_peewee() as model:
            if interval is not None:
                interval_order = interval.value
                s = (
                    model.select()
                        .where(
                        (model.code == symbol)
                        & (model.interval == interval_order)
                        & (model.yy_mm_dd ==  yy_mm_dd)
                    )
                        .order_by(model.hh_mm)
                )
            else:
                s = (
                    model.select()
                        .where(
                        (model.code == symbol)
                        & (model.yy_mm_dd == yy_mm_dd)
                    )
                        .order_by(model.hh_mm)
                )
            data = [db_bar for db_bar in s]
        return data


    def save_bars(self, bars:List[BarDataModel]):
        """
        保存Bar数据。
        """
        dicts = [i.to_dict() for i in bars]
        with self.source.use_peewee() as model:
            db = model._meta.database
            with db.atomic():
                for c in peewee.chunked(dicts, 50):
                    model.insert_many(c).on_conflict_replace().execute()

    def size(self,symbol:str = None)->int:
        with self.source.use_peewee() as model:
            if symbol is None:
                return model.select().count()
            else:
                return model.select().where( model.code == symbol).count()


    def clear_before(self,time:datetime):
        y_m_d, h_m_s = split_datetime(time)
        with self.source.use_peewee() as model:
            db = model._meta.database
            if isinstance(db, peewee.MySQLDatabase):
                """
                 mysql不适合删除执行delete操作，巨慢
                使用建表的方式去删除:
                1. create table new_table like old_table;
                2. insert into new_table select * from old_table where xxx
                3. drop old_table;（请确认备份好了）
                4. alter table new_table rename to old_table;
                """
                db = db
                table_new = f"{self.table_name}_bak"
                try:
                    cursor = db.execute_sql(f'drop table {table_new};')
                    cursor.fetchone()
                except Exception:
                    pass
                db.execute_sql(f'create table {table_new} like {self.table_name};').fetchone()
                db.execute_sql(f'insert into {table_new} select * from {self.table_name} where yy_mm_dd > {y_m_d};').fetchone()
                db.execute_sql(f'drop table {self.table_name};').fetchone()
                db.execute_sql(f'alter table {table_new} rename to {self.table_name};').fetchone()

            else:
                model.delete().where(
                    (model.yy_mm_dd<= y_m_d)
                ).execute()


    def clear(self, symbol: str = None):
        with self.source.use_peewee() as model:
            if not symbol is None:
                model.delete().where(
                    (model.code == symbol)
                ).execute()
            else:
                model.drop_table()
                model.create_table()

    def get_newest_datetime(self, symbol: str = None)-> Optional[datetime]:
        with self.source.use_peewee() as model:
            if symbol is None:
                s = (
                    model.select(model.yy_mm_dd)
                         .order_by(model.yy_mm_dd.desc())
                        .first()
                )
                if s:
                    return to_datetime(s.yy_mm_dd,the_day_end=True)
            else:
                s = (
                    model.select(model.yy_mm_dd)
                        .where(
                        (model.code == symbol)
                    )
                        .order_by(model.yy_mm_dd.desc())
                        .first()
                )
                if s:
                    return to_datetime(s.yy_mm_dd,the_day_end=True)
            return None

    def get_oldest_datetime(self, symbol: str = None,interval = Interval.DAILY) -> Optional[datetime]:
        interval = interval.value
        with self.source.use_peewee() as model:
            if symbol is None:
                s = (
                    model.select(model.yy_mm_dd)
                        .where(
                        (model.interval == interval)
                    )
                        .order_by(model.yy_mm_dd.asc())
                        .first()
                )
                if s:
                    return to_datetime(s.year_month_day)
            else:
                s = (
                    model.select(model.yy_mm_dd)
                        .where(
                        (model.interval == interval)
                        & (model.code == symbol)
                    )
                        .order_by(model.yy_mm_dd.asc())
                        .first()
                )
                if s:
                    return to_datetime(s.year_month_day)
        return None


    def load_day_list_with_yy_mm_sss(self, symbol: str = None, start: datetime = None,
                                     end: datetime = None) -> []:
        """"
        加载股票symbol的行情日
        """
        begin = 1
        finished = 99999999
        if not start is None:
            _y_m_d,_h_m_s = split_datetime(start)
            begin = _y_m_d
        if not end is None:
            _y_m_d,_h_m_s = split_datetime(end)
            finished = _y_m_d

        s = None
        with self.source.use_peewee() as model:
            if symbol is None:
                s = (model.select(model.yy_mm_dd).where(
                        ##(model.interval == interval)
                        (model.yy_mm_dd>= begin)
                        & (model.yy_mm_dd<= finished)
                    ).order_by(model.yy_mm_dd.asc()).distinct())
            else:
                s = (
                    model.select(model.yy_mm_dd).where(
                       # (model.interval == interval)
                        (model.code == symbol)
                        & (model.yy_mm_dd> begin)
                        & (model.yy_mm_dd< finished)
                    ).order_by(model.yy_mm_dd.asc()).distinct()
                )
            year_month_day_list = [db_bar.yy_mm_dd for db_bar in s]
        return year_month_day_list


